package cn.edu.hbwe.book.admin.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import cn.edu.hbwe.book.entity.Book;
import cn.edu.hbwe.book.utils.C3P0Util;

public class BookDao {
	private static QueryRunner run = new QueryRunner(C3P0Util.getDataSource());

	// 查找所有图书
	public List<Book> listAll() throws SQLException {
		String sql = "select * from products";
		return run.query(sql, new BeanListHandler<Book>(Book.class));
	}

	// 根据条件查询图书
	public List<Book> finBookCondition(String id, String name, String category, String minprice, String maxprice)
			throws SQLException {
		String sql = "select * from products where 1=1";
		List<Object> list = new ArrayList<Object>();
		if (id != null && id.trim().length() > 0) {
			sql += " and id=?";
			list.add(id);
		}
		if (name != null && name.trim().length() > 0) {
			sql += " and name=?";
			list.add(name);
		}
		if (category != null && category.trim().length() > 0) {
			sql += " and category=?";
			list.add(category);
		}
		if (minprice != null && maxprice != null && minprice.trim().length() > 0 && maxprice.trim().length() > 0) {
			sql += " and price between ? and ?";
			list.add(minprice);
			list.add(maxprice);
		}
		List<Book> lb = run.query(sql, new BeanListHandler<Book>(Book.class), list.toArray());
		return lb;
	}

	// 添加图书
	public void addBook(Book b) throws SQLException {
		String sql = "insert into products values(?,?,?,?,?,?,?)";
		run.update(sql, b.getId(), b.getName(), b.getPrice(), b.getCategory(), b.getPnum(), b.getImgurl(),
				b.getDescription());

	}

	// 根据id删除商品信息
	public void deleteBook(String id) throws SQLException {
		String sql = "delete from products where id = ?";
		run.update(sql, id);
	}

	// 修改商品信息
	public void editBook(Book b) throws SQLException {
		// 将商品信息添加到集合中
		List<Object> obj = new ArrayList<Object>();
		obj.add(b.getName());
		obj.add(b.getPrice());
		obj.add(b.getCategory());
		obj.add(b.getPnum());
		obj.add(b.getDescription());
		String sql = "update products set  name=?,price=?,category=?,pnum=?,description=? ";
		// 判断是否有图片
		if (b.getImgurl() != null && b.getImgurl().trim().length() > 0) {
			sql += " ,imgurl=?";
			obj.add(b.getImgurl());
		}
		sql += " where id=?";
		obj.add(b.getId());
		run.update(sql, obj.toArray());
	}

	// 销售榜单
	public List<Object[]> salesList(String year, String month) throws SQLException {
		String sql = "SELECT products.name,SUM(orderitem.buynum) totalsalnum FROM orders,products,orderItem WHERE orders.id=orderItem.order_id AND products.id=orderItem.product_id AND orders.paystate=1 and year(orderTime)=? and month(orderTime)=? GROUP BY products.name ORDER BY totalsalnum DESC";
		return run.query(sql, new ArrayListHandler(), year, month);
	}

}
